package br.com.project.persistence;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import br.com.project.entity.Answer;
import br.com.project.entity.Question;
import br.com.project.entity.Simulation;


public class DAO {
	
	private static DAO dao;
	private static DatabaseHelper databaseHelper;
	
	private DAO(Context context){
		databaseHelper = new DatabaseHelper(context);
	}
	
	public static DAO getInstance(Context context){
		if(dao == null){
			dao = new DAO(context);
		}
		
		return dao;
	}
	
//	public long insertStudent(Student student){
//		
//		int studentID = student.getId();
//		
//		SQLiteDatabase db = databaseHelper.getWritableDatabase();
//		
//		ContentValues cv = new ContentValues();
//		cv.put(DatabaseHelper.COL_STUDENT_ID, studentID);
//		cv.put(DatabaseHelper.COL_NAME, student.getName());
//		cv.put(DatabaseHelper.COL_LOGIN, student.getLogin());
//		cv.put(DatabaseHelper.COL_PASS, student.getPassword());
//		
//		long id = db.insert(DatabaseHelper.STUDENT_TABLE, null, cv);
//		
//		return id;
//		
//	}
//	
//	public List<Student> listStudent(){
//		SQLiteDatabase db = databaseHelper.getReadableDatabase();
//		
//		List<Student> students = new ArrayList<Student>();
//		
//		Cursor cursorStudent = db.query(DatabaseHelper.STUDENT_TABLE, 
//				new String[] { DatabaseHelper.COL_STUDENT_ID, DatabaseHelper.COL_NAME, DatabaseHelper.COL_LOGIN, DatabaseHelper.COL_PASS },
//				null, null, null, null, DatabaseHelper.COL_STUDENT_ID + " asc");
//				
//		if (cursorStudent.moveToFirst()) {
//			do {
//				int idIndex = cursorStudent.getColumnIndex(DatabaseHelper.COL_STUDENT_ID);
//				int nameIndex = cursorStudent.getColumnIndex(DatabaseHelper.COL_NAME);
//				int loginIndex = cursorStudent.getColumnIndex(DatabaseHelper.COL_LOGIN);
//				int passIndex = cursorStudent.getColumnIndex(DatabaseHelper.COL_PASS);
//				
//				int studentID = cursorStudent.getInt(idIndex);
//				String name = cursorStudent.getString(nameIndex);
//				String login = cursorStudent.getString(loginIndex);
//				String password = cursorStudent.getString(passIndex);
//				
//				Student student = new Student();
//				student.setId(studentID);
//				student.setName(name);
//				student.setLogin(login);
//				student.setPassword(password);
//				
//				students.add(student);
//				
//			} while (cursorStudent.moveToNext());
//		}
//		
//		if (cursorStudent != null && !cursorStudent.isClosed()) {
//			cursorStudent.close();
//		}
//		
//		db.close();
//		
//		return students;
//	}
//	
//	public boolean insertResult(Result result){
//		
////		if(!simulationExists(simulationID)){
//			SQLiteDatabase db = databaseHelper.getWritableDatabase();
//			
//			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
//
//			ContentValues cv = new ContentValues();
//			cv.put(DatabaseHelper.COL_RESULT_ID, result.getId());
//			cv.put(DatabaseHelper.COL_DATE, dateFormat.format(result.getDone()));
//			cv.put(DatabaseHelper.COL_GRADE, result.getGrade());
//			cv.put(DatabaseHelper.COL_SIMULATION_FK, result.getSimulation().getId());
//			cv.put(DatabaseHelper.COL_STUDENT_FK, result.getStudent().getId());
//			
//			Long id = db.insert(DatabaseHelper.RESULT_TABLE, DatabaseHelper.COL_RESULT_ID, cv);
//			
//			int resultID = Integer.parseInt(id.toString());
//			
//			List<ResultAnswer> resultAnswers = result.getStudentAnswers();
//			int resultAnswerID;
//			
//			for(ResultAnswer resultAnswer: resultAnswers){
//				
//				resultAnswerID = Integer.parseInt(insertResultAnswer(resultAnswer).toString());
//				insertResultResultAnswer(resultID, resultAnswerID);
//				
//			}
//			
//			db.close();
//			
//			return true;
////		} else {
////			return false;
////		}
//		
//	}
//	
//	public Long insertResultAnswer(ResultAnswer resultAnswer){
//		SQLiteDatabase db = databaseHelper.getWritableDatabase();
//		
//		int resultAnswerID = resultAnswer.getId();
//		
//		ContentValues cv = new ContentValues();
//		cv.put(DatabaseHelper.COL_RESULT_ANSWER_ID, resultAnswerID);
//		cv.put(DatabaseHelper.COL_ANSWERED_ID, resultAnswer.getAnsweredId());
//		cv.put(DatabaseHelper.COL_QUESTION_FK, resultAnswer.getQuestion().getId());
//		
//		Long id = db.insert(DatabaseHelper.RESULT_ANSWER_TABLE, null, cv);
//		db.close();
//		
//		return id;
//	}
//	
//	public void insertResultResultAnswer(int simulationID, int questionID){
//		SQLiteDatabase db = databaseHelper.getWritableDatabase();
//		
//		ContentValues cv = new ContentValues();
//		cv.put(DatabaseHelper.COL_RESULT_RR_ID, simulationID);
//		cv.put(DatabaseHelper.COL_RESULT_ANSWER_RR_ID, questionID);
//		
//		db.insert(DatabaseHelper.RESULT_RESULT_ANSWER_TABLE, null, cv);
//		db.close();
//	}
//	
//	public List<Result> listResult(){
//		SQLiteDatabase db = databaseHelper.getReadableDatabase();
//		
//		List<Result> simulations = new ArrayList<Result>();
//		
//		Cursor cursorSimulation = db.query(DatabaseHelper.RESULT_TABLE, 
//				new String[] { DatabaseHelper.COL_RESULT_ID, DatabaseHelper.COL_DATE, 
//								DatabaseHelper.COL_GRADE, DatabaseHelper.COL_SIMULATION_FK, DatabaseHelper.COL_STUDENT_FK },
//				null, null, null, null, DatabaseHelper.COL_RESULT_ID + " asc");
//				
//		if (cursorSimulation.moveToFirst()) {
//			do {
//				int idIndex = cursorSimulation.getColumnIndex(DatabaseHelper.COL_RESULT_ID);
//				int dateIndex = cursorSimulation.getColumnIndex(DatabaseHelper.COL_DATE);
//				int gradeIndex = cursorSimulation.getColumnIndex(DatabaseHelper.COL_GRADE);
//				int simulationFKIndex = cursorSimulation.getColumnIndex(DatabaseHelper.COL_SIMULATION_FK);
//				int studentFKIndex = cursorSimulation.getColumnIndex(DatabaseHelper.COL_STUDENT_FK);
//				
//				int resultID = cursorSimulation.getInt(idIndex);
//				String date = cursorSimulation.getString(dateIndex);
//				int grade = cursorSimulation.getInt(gradeIndex);
//				int simulationFK = cursorSimulation.getInt(simulationFKIndex);
//				int studentFK = cursorSimulation.getInt(studentFKIndex);
//				
//				Result result = new Result();
//				result.setId(resultID);
//				result.setGrade(grade);
//				
//				List<ResultAnswer> studentAnswers = listSimulationQuestions(db, resultID);
//				
//				result.setStudentAnswers(studentAnswers);
//				simulations.add(result);
//				
//			} while (cursorSimulation.moveToNext());
//		}
//		
//		if (cursorSimulation != null && !cursorSimulation.isClosed()) {
//			cursorSimulation.close();
//		}
//		
//		db.close();
//		
//		return simulations;
//	}
	
	public boolean insertSimulation(Simulation simulation){
		
		int simulationID = simulation.getId();
		
		if(!simulationExists(simulationID)){
			SQLiteDatabase db = databaseHelper.getWritableDatabase();
			
			ContentValues cv = new ContentValues();
			cv.put(DatabaseHelper.COL_SIMULATION_ID, simulationID);
			cv.put(DatabaseHelper.COL_TITLE, simulation.getTitle());
			cv.put(DatabaseHelper.COL_DESCRIPTION, simulation.getDescription());
			
			db.insert(DatabaseHelper.SIMULATION_TABLE, DatabaseHelper.COL_SIMULATION_ID, cv);
			
			List<Question> questions = simulation.getQuestions();
			int questionID;
			
			for(Question question: questions){
				
				questionID = question.getId();
				
				insertQuestion(question);
				insertSimulationQuestion(simulationID, questionID);
				
				List<Answer> answers = question.getAnswers();
				int answerID;
				
				for(Answer answer: answers){
					answerID = answer.getId();
					
					insertAnswer(answer);
					insertQuestionAnswer(questionID, answerID);
				}
			}
			
			db.close();
			
			return true;
		} else {
			return false;
		}
		
	}
	
	public void insertSimulationQuestion(int simulationID, int questionID){
		SQLiteDatabase db = databaseHelper.getWritableDatabase();
		
		ContentValues cv = new ContentValues();
		cv.put(DatabaseHelper.COL_SIMULATION_SQ_ID, simulationID);
		cv.put(DatabaseHelper.COL_QUESTION_SQ_ID, questionID);
		
		db.insert(DatabaseHelper.SIMULATION_QUESTION_TABLE, DatabaseHelper.COL_SIMULATION_SQ_ID, cv);
		db.close();
	}
	
	public void insertQuestion(Question question){
		SQLiteDatabase db = databaseHelper.getWritableDatabase();
		
		int questionID = question.getId();
		
		ContentValues cv = new ContentValues();
		cv.put(DatabaseHelper.COL_QUESTION_ID, questionID);
		cv.put(DatabaseHelper.COL_QUESTION, question.getQuestion());
		
		db.insert(DatabaseHelper.QUESTION_TABLE, null, cv);
		db.close();
	}
	
	public void insertQuestionAnswer(int questionID, int answerID){
		SQLiteDatabase db = databaseHelper.getWritableDatabase();
		
		ContentValues cv = new ContentValues();
		cv.put(DatabaseHelper.COL_QUESTION_QA_ID, questionID);
		cv.put(DatabaseHelper.COL_ANSWER_QA_ID, answerID);
		
		db.insert(DatabaseHelper.QUESTION_ANSWER_TABLE, DatabaseHelper.COL_QUESTION_QA_ID, cv);
		db.close();
	}
	
	public void insertAnswer(Answer answer){
		SQLiteDatabase db = databaseHelper.getWritableDatabase();
		
		int answerID = answer.getId();
		
		ContentValues cv = new ContentValues();
		cv.put(DatabaseHelper.COL_ANSWER_ID, answerID);
		cv.put(DatabaseHelper.COL_ANSWER, answer.getAnswer());
		cv.put(DatabaseHelper.COL_RIGHT, answer.isRight());
		
		db.insert(DatabaseHelper.ANSWER_TABLE, DatabaseHelper.COL_ANSWER_ID, cv);
		db.close();
	}
	
	public boolean simulationExists(int id){
		
		SQLiteDatabase db = databaseHelper.getReadableDatabase();
		
		Cursor cursorSimulation = db.query(DatabaseHelper.SIMULATION_TABLE, 
				new String[] { DatabaseHelper.COL_SIMULATION_ID, DatabaseHelper.COL_TITLE, DatabaseHelper.COL_DESCRIPTION },
				DatabaseHelper.COL_SIMULATION_ID + " = " + id, null, null, null, "title desc");
		
		if (cursorSimulation.getCount() > 0) {
			return true;
		}
		
		if (cursorSimulation != null && !cursorSimulation.isClosed()) {
			cursorSimulation.close();
		}
		
		db.close();
		
		return false;
	}
	
	public Simulation retrieveSimulation(int id){
		Simulation simulation = null;
		
		SQLiteDatabase db = databaseHelper.getReadableDatabase();
		
		Cursor cursorSimulation = db.query(DatabaseHelper.SIMULATION_TABLE, 
				new String[] { DatabaseHelper.COL_SIMULATION_ID, DatabaseHelper.COL_TITLE, DatabaseHelper.COL_DESCRIPTION },
				DatabaseHelper.COL_SIMULATION_ID + " = " + id, null, null, null, "title desc");
		
		if (cursorSimulation.moveToFirst()) {
			int idIndex = cursorSimulation.getColumnIndex(DatabaseHelper.COL_SIMULATION_ID);
			int titleIndex = cursorSimulation.getColumnIndex(DatabaseHelper.COL_TITLE);
			int descriptionIndex = cursorSimulation.getColumnIndex(DatabaseHelper.COL_DESCRIPTION);
			
			int simulationID = cursorSimulation.getInt(idIndex);
			String title = cursorSimulation.getString(titleIndex);
			String description = cursorSimulation.getString(descriptionIndex);
			
			simulation = new Simulation();
			simulation.setId(simulationID);
			simulation.setTitle(title);
			simulation.setDescription(description);
			
			List<Question> questions = listSimulationQuestions(db, simulationID);
			
			simulation.setQuestions(questions);
		}
		
		if (cursorSimulation != null && !cursorSimulation.isClosed()) {
			cursorSimulation.close();
		}
		
		db.close();
		
		return simulation;
	}
	
	public List<Simulation> listSimulations(){
		SQLiteDatabase db = databaseHelper.getReadableDatabase();
		
		List<Simulation> simulations = new ArrayList<Simulation>();
		
		Cursor cursorSimulation = db.query(DatabaseHelper.SIMULATION_TABLE, 
				new String[] { DatabaseHelper.COL_SIMULATION_ID, DatabaseHelper.COL_TITLE, DatabaseHelper.COL_DESCRIPTION },
				null, null, null, null, DatabaseHelper.COL_TITLE + " desc");
				
		if (cursorSimulation.moveToFirst()) {
			do {
				int idIndex = cursorSimulation.getColumnIndex(DatabaseHelper.COL_SIMULATION_ID);
				int titleIndex = cursorSimulation.getColumnIndex(DatabaseHelper.COL_TITLE);
				int descriptionIndex = cursorSimulation.getColumnIndex(DatabaseHelper.COL_DESCRIPTION);
				
				int simulationID = cursorSimulation.getInt(idIndex);
				String title = cursorSimulation.getString(titleIndex);
				String description = cursorSimulation.getString(descriptionIndex);
				
				Simulation simulation = new Simulation();
				simulation.setId(simulationID);
				simulation.setTitle(title);
				simulation.setDescription(description);
				
				List<Question> questions = listSimulationQuestions(db, simulationID);
				
				simulation.setQuestions(questions);
				simulations.add(simulation);
				
			} while (cursorSimulation.moveToNext());
		}
		
		if (cursorSimulation != null && !cursorSimulation.isClosed()) {
			cursorSimulation.close();
		}
		
		db.close();
		
		return simulations;
	}
	
	public List<Question> listSimulationQuestions(SQLiteDatabase db, int simulationID){
		String queryQuestion = "select q." + DatabaseHelper.COL_QUESTION_ID + ", q." + DatabaseHelper.COL_QUESTION
			+ " from " + DatabaseHelper.QUESTION_TABLE + " q join " + DatabaseHelper.SIMULATION_QUESTION_TABLE 
			+ " sq on q." + DatabaseHelper.COL_QUESTION_ID + " = sq." + DatabaseHelper.COL_QUESTION_SQ_ID 
			+ " where sq." + DatabaseHelper.COL_SIMULATION_SQ_ID + " = " + simulationID;
		
		Cursor cursorQuestion = db.rawQuery(queryQuestion,  null);
		List<Question> questions = new ArrayList<Question>();
		
		if (cursorQuestion.moveToFirst()) {
			do {
				int idQuestionIndex = cursorQuestion.getColumnIndex(DatabaseHelper.COL_QUESTION_ID);
				int questionIndex = cursorQuestion.getColumnIndex(DatabaseHelper.COL_QUESTION);
				
				int questionID = cursorQuestion.getInt(idQuestionIndex);
				String questionText = cursorQuestion.getString(questionIndex);
				
				Question question = new Question();
				question.setId(questionID);
				question.setQuestion(questionText);
				
				List<Answer> answers = listQuestionAnswers(db, questionID);
				
				question.setAnswers(answers);
				questions.add(question);
				
			} while (cursorQuestion.moveToNext());
		}
		
		if (cursorQuestion != null && !cursorQuestion.isClosed()) {
			cursorQuestion.close();
		}
		
		return questions;
	}
	
	public List<Answer> listQuestionAnswers(SQLiteDatabase db, int questionID){
		String queryAnswer = "select a." + DatabaseHelper.COL_ANSWER_ID + ", a." + DatabaseHelper.COL_ANSWER + ", a." + DatabaseHelper.COL_RIGHT
			+" from " + DatabaseHelper.ANSWER_TABLE + " a join " + DatabaseHelper.QUESTION_ANSWER_TABLE 
			+ " qa on a."+ DatabaseHelper.COL_ANSWER_ID + " = qa." + DatabaseHelper.COL_ANSWER_QA_ID 
			+ " where qa." + DatabaseHelper.COL_QUESTION_QA_ID + " = " + questionID;
		
		List<Answer> answers = new ArrayList<Answer>();
		
		Cursor cursorAnswer = db.rawQuery(queryAnswer,  null);
		
		if (cursorAnswer.moveToFirst()) {
			do {
				int idAnswerIndex = cursorAnswer.getColumnIndex(DatabaseHelper.COL_ANSWER_ID);
				int answerIndex = cursorAnswer.getColumnIndex(DatabaseHelper.COL_ANSWER);
				int rightIndex = cursorAnswer.getColumnIndex(DatabaseHelper.COL_RIGHT);
				
				int answerID = cursorAnswer.getInt(idAnswerIndex);
				String answerText = cursorAnswer.getString(answerIndex);
				boolean right = cursorAnswer.getInt(rightIndex)>0;
				
				Answer answer = new Answer();
				answer.setId(answerID);
				answer.setAnswer(answerText);
				answer.setRight(right);
				
				answers.add(answer);
				
			} while (cursorAnswer.moveToNext());
		}
		
		if (cursorAnswer != null && !cursorAnswer.isClosed()) {
			cursorAnswer.close();
		}
		
		return answers;
	}
	
//	public static void delete(Ticket ticket) {
//		try {
//			db.beginTransaction();
//
//			// delete this record
//			String delete = "DELETE FROM tickets WHERE id='" + ticket.getId()
//					+ "'";
//			db.execSQL(delete);
//
//			db.setTransactionSuccessful();
//		} finally {
//			db.endTransaction();
//		}
//	}
}
